package com.example;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.time.ZoneId;
import java.util.*;

/**
 * Excel → JSON（原始列头版）
 * 第一行直接当 JSON key，不做任何映射。
 */
public class ExcelToJson {

    private static final ObjectMapper MAPPER = new ObjectMapper()
            .enable(SerializationFeature.INDENT_OUTPUT)
            .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS);

    /**
     * 读取整个第一个 sheet，并把每行转成 Map&lt;header,value&gt;，
     * 最后输出 JSON 数组字符串。
     *
     * @param excelPath Excel 文件绝对路径（.xls/.xlsx）
     * @return JSON 数组字符串
     */
    public static String excelToJsonString(String excelPath) throws Exception {
        List<Map<String, Object>> rows = readSheet(excelPath);
        return MAPPER.writeValueAsString(rows);
    }

    /**
     * 读取 sheet 并把 JSON 数组写到指定文件。
     *
     * @param excelPath   Excel 文件绝对路径
     * @param jsonOutPath 目标 JSON 文件绝对路径
     */
    public static void excelToJsonFile(String excelPath, String jsonOutPath) throws Exception {
        String json = excelToJsonString(excelPath);
        try (FileOutputStream fos = new FileOutputStream(jsonOutPath)) {
            fos.write(json.getBytes("UTF-8"));
        }
    }

    // ────────────────────────────────────────────────────
    // 内部方法：读取第一个 sheet，首行为 key，后续行为 value。
    // ────────────────────────────────────────────────────
    private static List<Map<String, Object>> readSheet(String excelPath) throws Exception {
        File file = new File(excelPath);
        try (Workbook wb = WorkbookFactory.create(file)) {
            Sheet sheet = wb.getSheetAt(0);

            DataFormatter fmt = new DataFormatter();
            FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

            // 1. 读取表头
            Row headerRow = sheet.getRow(sheet.getFirstRowNum());
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(fmt.formatCellValue(cell).trim());
            }

            // 2. 读取数据行
            List<Map<String, Object>> list = new ArrayList<>();
            for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;

                Map<String, Object> obj = new LinkedHashMap<>();
                boolean allBlank = true;
                for (int c = 0; c < headers.size(); c++) {
                    Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Object value = getCellValue(cell, fmt, eval);
                    if (value != null && !"".equals(value.toString())) {
                        allBlank = false;
                    }
                    obj.put(headers.get(c), value);
                }
                if (!allBlank) {
                    list.add(obj);
                }
            }
            return list;
        }
    }

    /** 统一处理各种单元格类型（公式／日期／数值／字符串） */
    private static Object getCellValue(Cell cell, DataFormatter fmt, FormulaEvaluator eval) {
        if (cell == null) return "";
        CellType type = cell.getCellType();
        if (type == CellType.FORMULA) {
            type = eval.evaluateFormulaCell(cell);
        }
        switch (type) {
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue()
                            .atZone(ZoneId.systemDefault())
                            .toLocalDateTime()
                            .toString()
                            .replace('T', ' ');
                } else {
                    double d = cell.getNumericCellValue();
                    return (d == Math.rint(d)) ? (long) d : d;
                }
            case STRING:
                return cell.getStringCellValue().trim();
            case BLANK:
                return "";
            default:
                return fmt.formatCellValue(cell);
        }
    }
    @Test
    public void test() throws Exception {
        String excelPath = "D:\\heima\\interface\\src\\main\\resources\\excel\\BP2023101509291722785deck.xls";
        String jsonOutPath = "D:\\heima\\interface\\src\\main\\resources\\json\\BP2023101509291722785deck.json";
        ExcelToJson.excelToJsonFile(excelPath, jsonOutPath);
    }
}







